﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spMaint_Indexes]
#-- Purpose:		Provides a selective mechanism for index maintenance
#--	Last Update:	10/10/2016
#--					For a complete history - please review comments in Version
#--					control.
#-- Called By:		Scheduled Job
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spMaint_Indexes]
(
	@database_name			sysname,
	@skip_heaps_stats		bit			= 0
)
AS

SET NOCOUNT ON

--- The name of the database is a mandatory parameter
IF ISNULL(@database_name, '') = ''
  BEGIN
	RAISERROR ('The name of the database is required (@database_name)', 16, 1)
	RETURN
  END

--- Do Not Process tempdb
IF @database_name = 'tempdb'
	RETURN

--- Declare Local Variables
DECLARE @sCommand varchar(8000), @readonly bit, @offline bit
DECLARE @sResults varchar(8000), @major int, @sTemplateStats varchar(MAX)
DECLARE @tStats TABLE (
	[database_name] varchar(256),
	[table_name] varchar(1024),
	[stats_name] varchar(512),
	[rowcntpct] decimal(18, 2) NULL
)
  
--- Determine the state of the database
SELECT	@offline = ISNULL( [dbo].[fnConfig_IsDatabaseOffline] ( @database_name ), 0 ),
		@readonly = ISNULL( [dbo].[fnConfig_IsDatabaseReadOnly] ( @database_name ), 0 ),
		@major = CAST(PARSENAME(CONVERT(varchar,SERVERPROPERTY('ProductVersion')),4) AS int),
		@sTemplateStats = REPLACE('USE [VAR_DBNAME];
IF DB_NAME() <> ''tempdb''
	SELECT		DISTINCT TOP 30 PERCENT
				DB_NAME() [database_name],
				QUOTENAME(SCHEMA_NAME(t3.[schema_id])) + ''.'' + QUOTENAME(t3.[name]) [table_name],
				QUOTENAME(t1.[name]) [stats_name],
				t4.[rowcntpct]
	FROM		[sys].[stats] t1
	LEFT JOIN	[sys].[indexes] t2 ON t1.[object_id] = t2.[object_id]
				AND t1.[stats_id] = t2.[index_id]
	JOIN		[sys].[objects] t3 ON t1.[object_id] = t3.[object_id]
				AND t3.[is_ms_shipped] = 0
	JOIN		(
				SELECT		QUOTENAME(ss.[name]) + ''.'' + QUOTENAME(st.[name]) [table_name],
							AVG(CAST(ssi.[rowmodctr] as decimal(18, 2))) / AVG(ssi.[rowcnt]) [rowcntpct]
				FROM		[sys].[indexes] si
				JOIN		[sys].[sysindexes] ssi ON si.[object_id] = ssi.[id]
							AND si.[name] = ssi.[name]
				JOIN		[sys].[tables] st ON st.[object_id] = si.[object_id]
				JOIN		[sys].[schemas] ss ON ss.[schema_id] = st.[schema_id]
				WHERE		st.[is_ms_shipped] = 0		--- Only app indexes
							AND si.[index_id] <> 0		--- Ignore Heaps
							AND ssi.[rowcnt] > 1000		--- Only tables with more than 1000 rows
							AND ssi.[rowmodctr] > 0		--- Only indexes with changed data
				GROUP BY	QUOTENAME(ss.[name]) + ''.'' + QUOTENAME(st.[name])
				HAVING		AVG(CAST(ssi.[rowmodctr] as decimal(18, 2))) / AVG(ssi.[rowcnt]) > 0.05
				) t4 ON t4.[table_name] = QUOTENAME(SCHEMA_NAME(t3.[schema_id])) + ''.'' + QUOTENAME(t3.[name])
	WHERE		t2.[object_id] IS NULL
				AND (
					STATS_DATE(t1.object_id, t1.stats_id) < DATEADD(dd, -1, GETDATE())
					OR STATS_DATE(t1.object_id, t1.stats_id) IS NULL
				)
	ORDER BY	[rowcntpct] DESC', '[VAR_DBNAME]', QUOTENAME(@database_name))

--- Only attempt the backup on an ONLINE database
IF @offline = 0 AND @readonly = 0
  BEGIN
	--- Rebuild Table Heaps
	IF DATEPART(weekday, GETDATE()) = 7 AND @major > 9 AND ISNULL(@skip_heaps_stats, 0) = 0
	  BEGIN
		BEGIN TRY
			EXEC	[dbo].[rebuild_heaps] @exec_sql_commands = 1
		END TRY
		BEGIN CATCH
			--- Log the error - if possible
			SELECT @sResults = 'Failure running rebuild_heaps.'
			IF ERROR_NUMBER() IS NOT NULL
				SELECT @sResults = @sResults + CHAR(13) 
								+ 'ErrNo: ' + ISNULL(CAST(ERROR_NUMBER() as varchar(50)), '') + CHAR(13)
								+ 'ErrMsg: ' + ISNULL(ERROR_MESSAGE(), '') + CHAR(13)
								+ 'Proc: ' + ISNULL(ERROR_PROCEDURE(), '') + CHAR(13)
								+ 'LineNo: ' + ISNULL(CAST(ERROR_LINE() as varchar(10)), '')

			--- Print the error message to sysout
			PRINT @sResults

			--- Insert the error in the database
			INSERT INTO [dbo].[tblMaint_Log] (mnt_table_catalog, mnt_action_date, mnt_action_type, mnt_success, mnt_results)
				VALUES (@database_name, GETDATE(), 'rebuild_heaps', 0, @sResults)
		END CATCH
	  END

	--- Defrag or Rebuild Clustered and Non-Clustered Indexes
	SELECT	@sCommand = 'EXEC [dbo].[spMaint_IndexDefrag] @database_name = [' + @database_name + '];'
	EXEC	(@sCommand)

	----- Update Statistics	
	IF DATEPART(weekday, GETDATE()) = 7 AND ISNULL(@skip_heaps_stats, 0) = 0
	  BEGIN
		INSERT INTO @tStats EXEC (@sTemplateStats)
		DECLARE crsStats CURSOR FAST_FORWARD READ_ONLY FOR
			SELECT		REPLACE(
							REPLACE(
								REPLACE('USE [VAR_DBNAME]; UPDATE STATISTICS [VAR_TABLENAME] [VAR_STATSNAME] WITH FULLSCAN, COLUMNS;', '[VAR_DBNAME]', QUOTENAME([database_name]))
							, '[VAR_TABLENAME]', [table_name])
						, '[VAR_STATSNAME]', [stats_name])
			FROM		@tStats
			ORDER BY	[database_name],
						[table_name],
						[stats_name]
		OPEN crsStats
		FETCH NEXT FROM crsStats INTO @sCommand
		WHILE @@FETCH_STATUS <> -1
		BEGIN
			BEGIN TRY
				RAISERROR(@sCommand, 0, 1) WITH NOWAIT
				EXEC (@sCommand)

			END TRY
			BEGIN CATCH
				--- Log the error - if possible
				SELECT @sResults = 'The database ' + @database_name + ' failed to update statistics (' + @sCommand + ').'
				IF ERROR_NUMBER() IS NOT NULL
					SELECT @sResults = @sResults + CHAR(13) 
									+ 'ErrNo: ' + ISNULL(CAST(ERROR_NUMBER() as varchar(50)), '') + CHAR(13)
									+ 'ErrMsg: ' + ISNULL(ERROR_MESSAGE(), '') + CHAR(13)
									+ 'Proc: ' + ISNULL(ERROR_PROCEDURE(), '') + CHAR(13)
									+ 'LineNo: ' + ISNULL(CAST(ERROR_LINE() as varchar(10)), '')

				--- Print the error message to sysout
				PRINT @sResults

				--- Insert the error in the database
				INSERT INTO [dbo].[tblMaint_Log] (mnt_table_catalog, mnt_action_date, mnt_action_type, mnt_success, mnt_results)
					VALUES (@database_name, GETDATE(), 'update_stats', 0, @sResults)
			END CATCH

			FETCH NEXT FROM crsStats INTO @sCommand
		END
		CLOSE crsStats
		DEALLOCATE crsStats
	  END
  END
ELSE
	PRINT 'The database ' + @database_name + ' is not in ONLINE mode or is READ-ONLY'
	
SET NOCOUNT OFF
